set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
set spark.sql.crossJoin.enabled=true;
with resp_network_count as (
    select resp_network_code                                                                              --责任网点code
         , resp_network_name                                                                              --责任网点name
         , duty_network_type                                                                              --责任网点网点类型(基础资料)
         , duty_agent_code                                                                                --责任网点代理区编码(基础资料)
         , duty_agent_name                                                                                --责任网点代理区名称(基础资料)
         , dt                                                                   as date_time
         , sum(if(computer_flag = 1 and warn_worn_flag = 1, waybill_no_num, 0)) as worn_num               --破损量
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '质量工单', goods_value,
                  0))                                                           as zhiliang_goods_value   --质量-罚款金额
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '仲裁', goods_value,
                  0))                                                           as zhongcai_goods_value   --仲裁-罚款金额
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '线上理赔', goods_value,
                  0))                                                           as lipei_goods_value      --赔付-罚款金额
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '质量工单', waybill_no_num,
                  0))                                                           as zhiliang_worn_num      --质量-破损票数
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '仲裁', waybill_no_num,
                  0))                                                           as zhongcai_worn_num      --仲裁-破损票数
         , sum(if(computer_flag = 1 and warn_worn_flag = 1 and work_type = '线上理赔', waybill_no_num,
                  0))                                                           as lipei_worn_num         --赔付-破损票数
         , sum(
            if(computer_flag = 1 and warn_worn_flag = 1 and overtime_flag = 1 and work_type = '仲裁', waybill_no_num,
               0))                                                              as zhongcai_overtime_num  --仲裁-超时未处理票数
         , sum(
            if(computer_flag = 1 and warn_worn_flag = 1 and overtime_flag = 1 and work_type = '质量工单', waybill_no_num,
               0))                                                              as zhiliang_overtime_num  --质量-超时未处理票数
         , sum(if(computer_flag = 3 and warn_worn_flag = 0 and work_type = '仲裁', waybill_no_num,
                  0))                                                           as zhongcai_warn_worn_num --仲裁-预警破损
         , sum(if(computer_flag = 3 and warn_worn_flag = 0 and work_type = '质量工单', waybill_no_num,
                  0))                                                           as zhiliang_warn_worn_num --质量-预警破损
         , 0                                                                    as high_worn_num          --质量-高危破损
    from jms_dm.dm_sqs_worn_waybill_detail_dt
    where dt >= date_add('{{ execution_date | cst_ds }}', -89)
      and dt <= '{{ execution_date | cst_ds }}'
      and is_delete = 1
      and computer_flag in (1, 3)
    group by resp_network_code --责任网点code
           , resp_network_name --责任网点name
           , duty_network_type --责任网点网点类型(基础资料)
           , duty_agent_code   --责任网点管理`大区`(基础资料)
           , duty_agent_name   --责任网点管理大区名称(基础资料)
           , dt
),
     pick_network_count as (
         select pick_network_code                              as resp_network_code      --寄件网点code
              , pick_network_name                              as resp_network_name
              , pick_network_type                              as duty_network_type      --寄件网点网点类型(基础资料)
              , pick_agent_code                                as duty_agent_code        --寄件网点代理区编码(基础资料)
              , pick_agent_name                                as duty_agent_name
              , dt                                             as date_time              --日期
              , 0                                              as worn_num               --破损量
              , 0                                              as zhiliang_goods_value   --质量-罚款金额
              , 0                                              as zhongcai_goods_value   --仲裁-罚款金额
              , 0                                              as lipei_goods_value      --赔付-罚款金额
              , 0                                              as zhiliang_worn_num      --质量-破损票数
              , 0                                              as zhongcai_worn_num      --仲裁-破损票数
              , 0                                              as lipei_worn_num         --赔付-破损票数
              , 0                                              as zhongcai_overtime_num  --仲裁-超时未处理票数
              , 0                                              as zhiliang_overtime_num  --质量-超时未处理票数
              , 0                                              as zhongcai_warn_worn_num --仲裁-预警破损
              , 0                                              as zhiliang_warn_worn_num --质量-预警破损
              , sum(if(work_type = '质量工单', waybill_no_num, 0)) as high_worn_num          --质量-高危破损
         from jms_dm.dm_sqs_worn_waybill_detail_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -89)
           and dt <= '{{ execution_date | cst_ds }}'
           and is_delete = 1
           and computer_flag = 2
         group by pick_network_code
                , pick_network_name
                , pick_network_type
                , pick_agent_code
                , pick_agent_name
                , dt
     ),
     network_count as (
         select resp_network_code                                     --寄件网点code
              , max(resp_network_name)      as resp_network_name
              , duty_network_type                                     --寄件网点网点类型(基础资料)
              , duty_agent_code                                       --寄件网点代理区编码(基础资料)
              , max(duty_agent_name)        as duty_agent_name
              , date_time                                             --日期
              , max(worn_num)               as worn_num               --破损量
              , max(zhiliang_goods_value)   as zhiliang_goods_value   --质量-罚款金额
              , max(zhongcai_goods_value)   as zhongcai_goods_value   --仲裁-罚款金额
              , max(lipei_goods_value)      as lipei_goods_value      --赔付-罚款金额
              , max(zhiliang_worn_num)      as zhiliang_worn_num      --质量-破损票数
              , max(zhongcai_worn_num)      as zhongcai_worn_num      --仲裁-破损票数
              , max(lipei_worn_num)         as lipei_worn_num         --赔付-破损票数
              , max(zhongcai_overtime_num)  as zhongcai_overtime_num  --仲裁-超时未处理票数
              , max(zhiliang_overtime_num)  as zhiliang_overtime_num  --质量-超时未处理票数
              , max(zhongcai_warn_worn_num) as zhongcai_warn_worn_num --仲裁-预警破损
              , max(zhiliang_warn_worn_num) as zhiliang_warn_worn_num --质量-预警破损
              , max(high_worn_num)          as high_worn_num          --质量-高危破损
         from (
                  select *
                  from resp_network_count
                  union all
                  select *
                  from pick_network_count
              ) a
         group by resp_network_code
                , duty_network_type
                , duty_agent_code
                , date_time
     ),
     network_massage as (
         select t1.id
              , t1.code                                                                 --编号
              , t1.name                                                                 --名称
              , t1.network_type                                                         --网点类型
              , t1.virt_code                                                            --虚拟代理区code
              , t1.virt_name                                                            --虚拟代理区name
              , t1.manage_code                                                          --管理大区名称
              , t1.manage_name                                                          --管理大区名称
              , t1.regional_id                                                          --大区ID
              , t1.regional_desc                                                        --regional_desc
              , if(name = '承运商', t1.financial_center_code, t1.agent_code) as agent_code --代理区编码
              , if(name = '承运商', t1.financial_center_desc, t1.agent_name) as agent_name --代理区名称
              , t1.fran_code                                                            --加盟商编码
              , t1.fran_name                                                            --加盟商名称
              , t1.center_code                                                          --中心编码
              , t1.center_name                                                          --中心名称
              , t1.provider_id                                                          --省份ID
              , t1.provider_desc                                                        --省份
              , t1.city_id                                                              --城市ID
              , t1.city_desc                                                            --城市
              , t1.area_id                                                              --区/县id
              , t1.area_desc                                                            --区/县
              , t2.date_mid_desc                                          as date_time
              , t1.zone_id
              , t1.zone_code
              , t1.zone_name
              , t1.parent_area_code
              , t1.parent_area_name
         from (
                  select t1.id
                       , t1.code          --编号
                       , t1.name          --名称
                       , t1.network_type  --网点类型
                       , t1.virt_code     --虚拟代理区code
                       , t1.virt_name     --虚拟代理区name
                       , t1.manage_code   --管理大区名称
                       , t1.manage_name   --管理大区名称
                       , t1.regional_id   --大区ID
                       , t1.regional_desc --regional_desc
                       , t1.agent_code    --代理区编码
                       , t1.agent_name    --代理区名称
                       , t1.fran_code     --加盟商编码
                       , t1.fran_name     --加盟商名称
                       , t1.center_code   --中心编码
                       , t1.center_name   --中心名称
                       , t1.provider_id   --省份ID
                       , t1.provider_desc --省份
                       , t1.city_id       --城市ID
                       , t1.city_desc     --城市
                       , t1.area_id       --区/县id
                       , t1.area_desc     --区/县
                       , t1.zone_id
                       , t1.zone_code
                       , t1.zone_name
                       , t1.financial_center_code
                       , t1.financial_center_desc
                       , t1.parent_area_code
                       , t1.parent_area_name
                  from jms_dim.dim_network_whole_massage t1
                  where (
                          name = '承运商'
                          or (agent_name not like '%BEST%'
                          and agent_name not like '%总部%'
                          and agent_name not like '%测试%')
                      )
                    and network_type <> 1
                    and is_virtual_agent <> 1
                    and is_enable <> 2
                  union all
                  select '70'      as id
                       , 'Test001' as code
                       , '承运商'     as name
                       , '2'       as network_type  --网点类型
                       , 'Test001' as virt_code     --虚拟代理区code
                       , '承运商'     as virt_name     --虚拟代理区name
                       , null      as manage_code   --管理大区名称
                       , null      as manage_name   --管理大区名称
                       , null      as regional_id   --大区ID
                       , null      as regional_desc --regional_desc
                       , 'Test001' as agent_code    --代理区编码
                       , '承运商'     as agent_name    --代理区名称
                       , null      as fran_code     --加盟商编码
                       , null      as fran_name     --加盟商名称
                       , null      as center_code   --中心编码
                       , null      as center_name   --中心名称
                       , null      as provider_id   --省份ID
                       , null      as provider_desc --省份
                       , null      as city_id       --城市ID
                       , null      as city_desc     --城市
                       , null      as area_id       --区/县id
                       , null      as area_desc     --区/县
                       , null      as zone_id
                       , null      as zone_code
                       , null      as zone_name
                       , 'Test001' as financial_center_code
                       , '承运商'     as financial_center_desc
                       , null      as parent_area_code
                       , null      as parent_area_name
              ) t1
                  join
              (
                  select date_mid_desc
                  from jms_dim.dim_date_info_base
                  where date_mid_desc >= date_add('{{ execution_date | cst_ds }}', -89)
                    and date_mid_desc <= '{{ execution_date | cst_ds }}'
              ) t2
              on 1 = 1
     )
insert
overwrite
table
bidefault.dm_sqs_worn_waybill_summary_dt
partition
(
dt
)
select t1.id                                  as resp_network_id        --责任网点id
     , t1.code                                as resp_network_code      --责任网点code
     , t1.name                                as resp_network_name      --责任网点name
     , t1.network_type                        as duty_network_type      --责任网点网点类型(基础资料)
     , nvl(t1.virt_code, t1.agent_code)       as duty_virt_code         --责任网点虚拟代理区code(基础资料)
     , nvl(t1.virt_name, t1.agent_name)       as duty_virt_name         --责任网点虚拟代理区name(基础资料)
     , t1.manage_code                         as duty_manage_code       --责任网点管理`大区`(基础资料)
     , t1.manage_name                         as duty_manage_name       --责任网点管理大区名称(基础资料)
     , t1.regional_id                         as duty_regional_id       --责任网点大区ID(基础资料)
     , t1.regional_desc                       as duty_regional_desc     --责任网点大区name(基础资料)
     , t1.agent_code                          as duty_agent_code        --责任网点代理区编码(基础资料)
     , t1.agent_name                          as duty_agent_name        --责任网点代理区名称(基础资料)
     , t1.fran_code                           as duty_fran_code         --责任网点加盟商编码(基础资料)
     , t1.fran_name                           as duty_fran_name         --责任网点加盟商名称(基础资料)
     , t1.center_code                         as duty_center_code       --责任网点中心编码(基础资料)
     , t1.center_name                         as duty_center_name       --责任网点中心名称(基础资料)
     , t1.provider_id                         as duty_provider_id       --责任网点省份ID(基础资料)
     , t1.provider_desc                       as duty_provider_desc     --责任网点省份(基础资料)
     , t1.city_id                             as duty_city_id           --责任网点城市ID(基础资料)
     , t1.city_desc                           as duty_city_desc         --责任网点城市(基础资料)
     , t1.area_id                             as duty_area_id           --责任网点区/县id'(基础资料)
     , t1.area_desc                           as duty_area_desc         --责任网点区/县'(基础资料)
     , t1.date_time                           as date_time              --日期
     , coalesce(t2.worn_num, 0)               as worn_num               --破损量
     , coalesce(t2.zhiliang_worn_num, 0)      as zhiliang_worn_num      --质量-破损票数
     , coalesce(t2.zhongcai_worn_num, 0)      as zhongcai_worn_num      --仲裁-破损票数
     , coalesce(t2.lipei_worn_num, 0)         as lipei_worn_num         --赔付-破损票数
     , coalesce(t2.zhiliang_goods_value, 0)   as zhiliang_goods_value   --质量-罚款金额
     , coalesce(t2.zhongcai_goods_value, 0)   as zhongcai_goods_value   --仲裁-罚款金额
     , coalesce(t2.lipei_goods_value, 0)      as lipei_goods_value      --赔付-罚款金额
     , coalesce(t2.goods_value, 0)            as goods_value            --总金额
     , t3.site_ops                            as tab_num                --操作量
     , 0                                      as worn_rate              --破损票数÷操作量*1000000
     , coalesce(t2.flag, 0)                   as flag                   --1直接根据责任网点进行统计2根据所属代理区进行统计0是不在工单里的网点标识
     , null                                   as remark1                --备用
     , null                                   as remark2                --备用
     , null                                   as remark3                --备用
     , t1.zone_id
     , t1.zone_code
     , t1.zone_name
     , coalesce(t2.zhongcai_overtime_num, 0)  as zhongcai_overtime_num  --仲裁-超时未处理票数
     , coalesce(t2.zhiliang_overtime_num, 0)  as zhiliang_overtime_num  --质量-超时未处理票数
     , coalesce(t2.zhongcai_warn_worn_num, 0) as zhongcai_warn_worn_num --仲裁-预警破损
     , coalesce(t2.zhiliang_warn_worn_num, 0) as zhiliang_warn_worn_num --质量-预警破损
     , coalesce(t2.warn_worn_num, 0)          as warn_worn_num          --总预警
     , coalesce(t2.high_worn_num, 0)          as high_worn_num          --质量-高危破损
     , t1.parent_area_code                    as duty_parent_area_code
     , t1.parent_area_name                    as duty_parent_area_name
     , t1.date_time                           as dt
from network_massage t1
         left join
     (
         select resp_network_code                                                                  --责任网点code
              , resp_network_name                                                                  --责任网点name
              , duty_network_type
              , duty_agent_code                                                                    --责任网点代理区编码(基础资料)
              , duty_agent_name                                                                    --责任网点代理区名称(基础资料)
              , date_time                                                                          --日期
              , worn_num                                                                           --破损量
              , zhiliang_worn_num                                                                  --质量-破损票数
              , zhongcai_worn_num                                                                  --仲裁-破损票数
              , lipei_worn_num                                                                     --赔付-破损票数
              , zhiliang_goods_value                                                               --质量-罚款金额
              , zhongcai_goods_value                                                               --仲裁-罚款金额
              , lipei_goods_value                                                                  --赔付-罚款金额
              , (zhiliang_goods_value + zhongcai_goods_value + lipei_goods_value) as goods_value   --总金额
              , zhongcai_overtime_num                                                              --仲裁-超时未处理票数
              , zhiliang_overtime_num                                                              --质量-超时未处理票数
              , zhongcai_warn_worn_num                                                             --仲裁-预警破损
              , zhiliang_warn_worn_num                                                             --质量-预警破损
              , (zhongcai_warn_worn_num + zhiliang_warn_worn_num)                 as warn_worn_num --总预警
              , high_worn_num                                                     as high_worn_num --质量-高危破损
              , 1                                                                 as flag          --直接根据责任网点进行统计
         from network_count t1
         where duty_network_type in (6, 5, 4, 3)
         union all
         select duty_agent_code                                                                  as resp_network_code      --责任网点code
              , if(duty_agent_name = '测试代理001', '承运商', duty_agent_name)                          as resp_network_name      --责任网点name
              , 2                                                                                as duty_network_type
              , duty_agent_code                                                                  as duty_agent_code        --责任网点代理区编码(基础资料)
              , if(duty_agent_name = '测试代理001', '承运商', duty_agent_name)                          as duty_agent_name        --责任网点代理区名称(基础资料)
              , date_time                                                                        as date_time              --日期
              , sum(worn_num)                                                                    as worn_num               --破损量
              , sum(zhiliang_worn_num)                                                           as zhiliang_worn_num      --质量-破损票数
              , sum(zhongcai_worn_num)                                                           as zhongcai_worn_num      --仲裁-破损票数
              , sum(lipei_worn_num)                                                              as lipei_worn_num         --赔付-破损票数
              , sum(zhiliang_goods_value)                                                        as zhiliang_goods_value   --质量-罚款金额
              , sum(zhongcai_goods_value)                                                        as zhongcai_goods_value   --仲裁-罚款金额
              , sum(lipei_goods_value)                                                           as lipei_goods_value      --赔付-罚款金额
              , (sum(zhiliang_goods_value) + sum(zhongcai_goods_value) + sum(lipei_goods_value)) as goods_value            --总金额
              , sum(zhongcai_overtime_num)                                                       as zhongcai_overtime_num  --仲裁-超时未处理票数
              , sum(zhiliang_overtime_num)                                                       as zhiliang_overtime_num  --质量-超时未处理票数
              , sum(zhongcai_warn_worn_num)                                                      as zhongcai_warn_worn_num --仲裁-预警破损
              , sum(zhiliang_warn_worn_num)                                                      as zhiliang_warn_worn_num --质量-预警破损
              , (sum(zhongcai_warn_worn_num) + sum(zhiliang_warn_worn_num))                      as warn_worn_num          --总预警
              , sum(high_worn_num)                                                               as high_worn_num          --质量-高危破损
              , 2                                                                                as flag                   --根据所属代理区进行统计
         from network_count
         group by duty_agent_code
                , duty_agent_name
                , date_time
     ) t2
     on t1.code = t2.resp_network_code
         and t1.date_time = t2.date_time
         left join
     (
         select site_code as site_code
              , site_ops
              , dt
         from jms_dws.dws_tbl_site_scan_operation_stat_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -89)
           and dt <= '{{ execution_date | cst_ds }}'
         union all
         select agent_code    as site_code
              , sum(site_ops) as site_ops
              , dt
         from jms_dws.dws_tbl_site_scan_operation_stat_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -89)
           and dt <= '{{ execution_date | cst_ds }}'
           and site_type = '6'
         group by agent_code
                , dt
     ) t3
     on t1.code = t3.site_code
         and t1.date_time = t3.dt
    distribute by dt
;
